Imports System
Imports System.Web.Security
Imports System.Web
Imports System.Web.Configuration
Imports System.Security.Principal
Imports System.Security.Permissions
Imports System.Globalization
Imports System.Runtime.Serialization
Imports System.Collections
Imports System.Collections.Specialized
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Text
Imports System.Configuration.Provider
Imports System.Configuration
Imports System.Web.Hosting
Imports System.Web.Util
Imports System.Security.Cryptography
Imports nsDataProvider = KDESolutions.Providers.DataProvider

Public NotInheritable Class DBRoleProvider
    Inherits RoleProvider

    Private _ApplicationName As String
    Private _SchemaVersionCheck As Integer
    Private _AppId As String
    Private _TablePrefix As String
    Private _DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString As String

#Region "Public Properties"
    Public Overrides Property ApplicationName() As String
        Get
            Return _ApplicationName
        End Get
        Set(ByVal value As String)
            _ApplicationName = value
            If (_ApplicationName.Length > 255) Then
                Throw New ProviderException("Provider application name too long")
            End If
        End Set
    End Property

    Public Property MyDBType() As String
        Get
            Return _DBType
        End Get
        Set(ByVal value As String)
            _DBType = value
        End Set
    End Property
#End Region

#Region "Public Methods"
    Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
        If String.IsNullOrEmpty(config("dataProviderGroup")) Then
            Throw New HttpException("You must provide a dataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("dataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
                Me.MyDBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If
        ' Remove CAS from sample: HttpRuntime.CheckAspNetHostingPermission (AspNetHostingPermissionLevel.Low, SR.Feature_not_supported_at_this_level);
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If
        If String.IsNullOrEmpty(name) Then
            name = "DBRoleProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "RoleDBProvider")
        End If
        MyBase.Initialize(name, config)
        _SchemaVersionCheck = 0
        _ApplicationName = config("applicationName")
        _TablePrefix = GetConfigValue(config("tablePrefix"), "aspnet")
        _AppId = GetAppId()
        If String.IsNullOrEmpty(_ApplicationName) Then
            _ApplicationName = GetDefaultAppName()
        End If
        If (_ApplicationName.Length > 255) Then
            Throw New ProviderException("Provider application name too long")
        End If
        config.Remove("applicationName")
        config.Remove("productKey")
        config.Remove("activationDate")
        config.Remove("tablePrefix")
        config.Remove("dataProviderGroup")
        If (config.Count > 0) Then
            Dim attribUnrecognized As String = config.GetKey(0)
            If Not String.IsNullOrEmpty(attribUnrecognized) Then
                Throw New ProviderException("Provider unrecognized attribute, " & attribUnrecognized)
            End If
        End If
    End Sub

    Public Overrides Function IsUserInRole(ByVal username As String, ByVal roleName As String) As Boolean
        CheckParameter(roleName, True, True, True, 255, "roleName")
        CheckParameter(username, True, False, True, 255, "username")
        If (username.Length < 1) Then
            Return False
        End If
        Try
            CheckSchemaVersion()
            Dim iStatus As Integer = UsersInRoles_IsUserInRole(username, roleName)
            Select Case (iStatus)
                Case 0
                    Return False
                Case 1
                    Return True
                Case 2
                    Return False
                    ' throw new ProviderException(SR.GetString(SR.Provider_user_not_found));
                Case 3
                    Return False
                    ' throw new ProviderException(SR.GetString(SR.Provider_role_not_found, roleName));
            End Select
            Throw New ProviderException("Provider unknown failure")
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Function GetRolesForUser(ByVal username As String) As String()
        CheckParameter(username, True, False, True, 255, "username")
        If (username.Length < 1) Then
            Return New String((0) - 1) {}
        End If
        CheckSchemaVersion()
        Dim tmpRoleNames As String = ""
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sql As String
        Dim UserId As String = String.Empty
        sql = "SELECT UserId FROM " & _TablePrefix & "_Users " & _
        "WHERE LoweredUserName = ? AND ApplicationId = ?"
        DAP.AddParameter("@LoweredUserName", username.ToLower, DbType.String)
        DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
        UserId = DAP.ExecuteScalar(sql)
        If String.IsNullOrEmpty(UserId) Then
            Throw New ProviderException("Could not find id for user, " & username)
        End If
        sql = "SELECT r.RoleName FROM " & _
        _TablePrefix & "_UsersInRoles ur, " & _TablePrefix & "_Roles r " & _
        "WHERE ur.UserId = ? " & _
        "AND ur.RoleId = r.RoleId " & _
        "AND r.ApplicationId = ?"
        DAP.AddParameter("@UserId", UserId, DbType.String)
        DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
        Dim reader As New Generic.List(Of Common.DbDataRecord)
        reader = DAP.ExecuteReader(sql)
        Try
            For Each recordItem As Common.DbDataRecord In reader
                tmpRoleNames = (tmpRoleNames & recordItem.Item("RoleName") & ",")
            Next

            If (tmpRoleNames.Length > 0) Then
                ' Remove trailing comma.
                tmpRoleNames = tmpRoleNames.Substring(0, (tmpRoleNames.Length - 1))
                Return tmpRoleNames.Split(Microsoft.VisualBasic.ChrW(44))
            End If
            Return New String((0) - 1) {}
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Sub CreateRole(ByVal roleName As String)
        CheckParameter(roleName, True, True, True, 255, "roleName")
        CheckSchemaVersion()
        Dim returnValue As Integer = Roles_CreateRole(roleName)
        Select Case (returnValue)
            Case 0
                Return
            Case 1
                Throw New ProviderException("Provider role already exists, " & roleName)
            Case Else
                Throw New ProviderException("Provider unknown failure")
        End Select
    End Sub

    Public Overrides Function DeleteRole(ByVal roleName As String, ByVal throwOnPopulatedRole As Boolean) As Boolean
        CheckParameter(roleName, True, True, True, 255, "roleName")
        Try
            CheckSchemaVersion()

            Dim returnValue As Integer = Roles_DeleteRole(roleName, throwOnPopulatedRole)

            If (returnValue = 2) Then
                Throw New ProviderException("Role is not empty")
            End If
            Return (returnValue = 0)
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Function RoleExists(ByVal roleName As String) As Boolean
        CheckParameter(roleName, True, True, True, 255, "roleName")
        Try
            CheckSchemaVersion()
            Return Roles_RoleExists(roleName)
            Throw New ProviderException("Provider unknown failure")
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Sub AddUsersToRoles(ByVal usernames() As String, ByVal roleNames() As String)
        CheckArrayParameter(usernames, True, True, True, 255, "username")
        CheckArrayParameter(roleNames, True, True, True, 255, "rolename")
        For Each rolename As String In roleNames
            If Not RoleExists(rolename) Then
                Throw New ProviderException("Role name not found.")
            End If
        Next
        For Each username As String In usernames
            If (username.IndexOf(Microsoft.VisualBasic.ChrW(44)) > 0) Then
                Throw New ArgumentException("User names cannot contain commas.")
            End If
        Next
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            For Each username As String In usernames
                For Each rolename As String In roleNames
                    If IsUserInRole(username, rolename) = False Then
                        Dim UserId As String = String.Empty
                        Dim RoleId As String = String.Empty
                        Dim sql As String
                        sql = "SELECT UserId FROM " & _TablePrefix & "_Users " & _
                        "WHERE ApplicationId = ? AND LoweredUserName = ?"
                        DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                        DAP.AddParameter("@LoweredUserName", username.ToLower, DbType.String)
                        UserId = DAP.ExecuteScalar(sql)
                        If String.IsNullOrEmpty(UserId) Then
                            Throw New ProviderException("Could not find user, " & username)
                        End If
                        sql = "SELECT RoleId FROM " & _TablePrefix & "_Roles " & _
                        "WHERE ApplicationId = ? AND LoweredRoleName = ?"
                        DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                        DAP.AddParameter("@LoweredRoleName", rolename.ToLower, DbType.String)
                        RoleId = DAP.ExecuteScalar(sql)
                        If String.IsNullOrEmpty(RoleId) Then
                            Throw New ProviderException("Could not find role, " & rolename)
                        End If
                        sql = "INSERT INTO " & _TablePrefix & "_UsersInRoles(UserId,RoleId) " & _
                        " Values(?, ?)"
                        DAP.AddParameter("@UserId", UserId, DbType.String)
                        DAP.AddParameter("@RoleID", RoleId, DbType.String)
                        If DAP.ExecuteNonQuery(sql) <> 1 Then
                            Throw New ProviderException("Error adding roles for users.")
                        End If
                    End If
                Next
            Next
        Finally
            DAP.Dispose()
        End Try
    End Sub

    Public Overrides Sub RemoveUsersFromRoles(ByVal usernames() As String, ByVal roleNames() As String)
        CheckSchemaVersion()
        CheckArrayParameter(usernames, True, True, True, 255, "username")
        CheckArrayParameter(roleNames, True, True, True, 255, "rolename")
        For Each rolename As String In roleNames
            If Not RoleExists(rolename) Then
                Throw New ProviderException("Role name not found.")
            End If
        Next
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            For Each username As String In usernames
                For Each rolename As String In roleNames
                    If IsUserInRole(username, rolename) Then
                        Dim UserId As String = String.Empty
                        Dim RoleId As String = String.Empty
                        Dim sql As String
                        sql = "SELECT UserId FROM " & _TablePrefix & "_Users " & _
                        "WHERE LoweredUserName = ? AND ApplicationId = ?"
                        DAP.AddParameter("@LoweredUserName", username.ToLower, DbType.String)
                        DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                        UserId = DAP.ExecuteScalar(sql)
                        If String.IsNullOrEmpty(UserId) Then
                            Throw New ProviderException("Could not find id for user, " & username)
                        End If
                        sql = "SELECT RoleId FROM " & _TablePrefix & "_Roles " & _
                        "WHERE LoweredRoleName = ? AND ApplicationId = ?"
                        DAP.AddParameter("@LoweredRoleName", rolename.ToLower, DbType.String)
                        DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                        RoleId = DAP.ExecuteScalar(sql)
                        If String.IsNullOrEmpty(RoleId) Then
                            Throw New ProviderException("Could not find id for role, " & rolename)
                        End If
                        sql = "DELETE FROM " & _TablePrefix & "_UsersInRoles " & _
                        "WHERE UserId = ? AND RoleId = ?"
                        DAP.AddParameter("@UserId", UserId, DbType.String)
                        DAP.AddParameter("@RoleId", RoleId, DbType.String)
                        DAP.ExecuteNonQuery(sql)
                    End If
                Next
            Next
        Finally
            DAP.Dispose()
        End Try
    End Sub

    Public Overrides Function GetUsersInRole(ByVal roleName As String) As String()
        CheckParameter(roleName, True, True, True, 255, "roleName")
        CheckSchemaVersion()
        Dim tmpUserNames As String = ""
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim RoleId As String = String.Empty
            Dim sql As String
            sql = "SELECT RoleId FROM " & _TablePrefix & "_Roles " & _
            "WHERE LoweredRoleName = ? AND ApplicationId = ?"
            DAP.AddParameter("@LoweredRoleName", roleName.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            RoleId = DAP.ExecuteScalar(sql)
            If String.IsNullOrEmpty(RoleId) Then
                Throw New ProviderException("Provider role not found, " & roleName)
            End If
            sql = "SELECT u.Username FROM " & _
            _TablePrefix & "_UsersInRoles ur, " & _TablePrefix & "_Users u " & _
            "WHERE ur.RoleId = ? AND ur.UserId = u.UserId"
            DAP.AddParameter("@RoleId", RoleId, DbType.String)
            Dim reader As Generic.List(Of Common.DbDataRecord)
            reader = DAP.ExecuteReader(sql)

            For Each resultItem As Common.DbDataRecord In reader
                tmpUserNames = (tmpUserNames & (resultItem.GetString(0) + ","))
            Next

            If (tmpUserNames.Length > 0) Then
                ' Remove trailing comma.
                tmpUserNames = tmpUserNames.Substring(0, (tmpUserNames.Length - 1))
                Return tmpUserNames.Split(Microsoft.VisualBasic.ChrW(44))
            End If
            Return New String((0) - 1) {}
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Function GetAllRoles() As String()
        Try
            CheckSchemaVersion()
            Dim result As New Generic.List(Of Common.DbDataRecord)
            result = Roles_GetAllRoles()
            Dim sc As StringCollection = New StringCollection
            For Each reader As Common.DbDataRecord In result
                sc.Add(reader.GetString(0))
            Next
            Dim strReturn(result.Count - 1) As String
            sc.CopyTo(strReturn, 0)
            Return strReturn
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Function FindUsersInRole(ByVal roleName As String, ByVal usernameToMatch As String) As String()
        CheckParameter(roleName, True, True, True, 255, "roleName")
        CheckParameter(usernameToMatch, True, True, False, 255, "usernameToMatch")
        CheckSchemaVersion()
        Dim tmpUserNames As String = ""
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim RoleId As String = String.Empty
            Dim sql As String
            sql = "SELECT RoleId FROM " & _TablePrefix & "_Roles " & _
            "WHERE LoweredRoleName = ? AND ApplicationId = ?"
            DAP.AddParameter("@LoweredRoleName", roleName.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            RoleId = DAP.ExecuteScalar(sql)
            If String.IsNullOrEmpty(RoleId) Then
                Throw New ProviderException("Provider role not found, " & roleName)
            End If
            sql = "SELECT u.Username FROM " & _
            _TablePrefix & "_UsersInRoles ur, " & _TablePrefix & "_Users u " & _
            "WHERE ur.RoleId = ? AND ur.UserId = u.UserId AND u.LoweredUserName LIKE ?"
            DAP.AddParameter("@RoleId", RoleId, DbType.String)
            DAP.AddParameter("@LoweredUserName", "%" & usernameToMatch.ToLower & "%", DbType.String)
            Dim reader As Generic.List(Of Common.DbDataRecord)
            reader = DAP.ExecuteReader(sql)

            For Each resultItem As Common.DbDataRecord In reader
                tmpUserNames = (tmpUserNames & (resultItem.GetString(0) + ","))
            Next

            If (tmpUserNames.Length > 0) Then
                ' Remove trailing comma.
                tmpUserNames = tmpUserNames.Substring(0, (tmpUserNames.Length - 1))
                Return tmpUserNames.Split(Microsoft.VisualBasic.ChrW(44))
            End If
            Return New String((0) - 1) {}
        Finally
            DAP.Dispose()
        End Try
    End Function
#End Region

#Region "Private Helper Methods"
    Private Sub CheckSchemaVersion()
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim features(0) As String
        features(0) = "Role Manager"

        Dim version As String = "1"
        If (features Is Nothing) Then
            Throw New ArgumentNullException("features")
        End If
        If (version Is Nothing) Then
            Throw New ArgumentNullException("version")
        End If
        Dim iStatus As Integer = 0
        For Each feature As String In features
            Dim sSQL As String = "SELECT COUNT(*) As SqlCount " & _
            "FROM " & _TablePrefix & "_SchemaVersions " & _
            "WHERE Feature = ? " & _
            "AND CompatibleSchemaVersion = ?"
            DAP.AddParameter("@Feature", feature, DbType.String)
            DAP.AddParameter("@CompatibleSchemaVersion", version, DbType.Int16)
            iStatus = DAP.ExecuteScalar(sSQL)
            If (iStatus = 0) Then
                _SchemaVersionCheck = -1
                Throw New ProviderException("Provider Schema Version Not Match " & MyBase.ToString & " version")
            End If
        Next
        If (_SchemaVersionCheck = -1) Then
            Throw New ProviderException("Provider Schema Version Not Match " & MyBase.ToString & " " & version)
        ElseIf (_SchemaVersionCheck = 0) Then
            If (_SchemaVersionCheck = -1) Then
                Throw New ProviderException("Provider Schema Version Not Match " & MyBase.ToString & " " & version)
            ElseIf (_SchemaVersionCheck = 0) Then
                _SchemaVersionCheck = 1
            End If
        End If
        DAP.Dispose()
    End Sub

    Private Function GetConfigValue(ByVal configValue As String, ByVal defaultValue As String) As String
        If String.IsNullOrEmpty(configValue) Then
            Return defaultValue
        End If
        Return configValue
    End Function

    Private Shared Function GetDefaultAppName() As String
        Try
            Dim appName As String = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath
            If String.IsNullOrEmpty(appName) Then
                appName = System.Diagnostics.Process.GetCurrentProcess.MainModule.ModuleName
                Dim indexOfDot As Integer = appName.IndexOf(ChrW(46))
                If (indexOfDot <> -1) Then
                    appName = appName.Remove(indexOfDot)
                End If
            End If
            If String.IsNullOrEmpty(appName) Then
                Return "/"
            Else
                Return appName
            End If
        Catch Ex As System.Exception
            Return "/"
        End Try
    End Function

    Friend Shared Function ValidateParameter(ByRef param As String, ByVal checkForNull As Boolean, ByVal checkIfEmpty As Boolean, ByVal checkForCommas As Boolean, ByVal maxSize As Integer) As Boolean
        If (param Is Nothing) Then
            Return Not checkForNull
        End If
        param = param.Trim
        If ((checkIfEmpty _
                    AndAlso (param.Length < 1)) _
                    OrElse (((maxSize > 0) _
                    AndAlso (param.Length > maxSize)) _
                    OrElse (checkForCommas AndAlso param.Contains(",")))) Then
            Return False
        End If
        Return True
    End Function

    Friend Shared Sub CheckParameter(ByRef param As String, ByVal checkForNull As Boolean, ByVal checkIfEmpty As Boolean, ByVal checkForCommas As Boolean, ByVal maxSize As Integer, ByVal paramName As String)
        If (param Is Nothing) Then
            If checkForNull Then
                Throw New ArgumentNullException(paramName)
            End If
            Return
        End If
        param = param.Trim
        If (checkIfEmpty _
                    AndAlso (param.Length < 1)) Then
            Throw New ArgumentException("Parameter can not be empty, " & paramName)
        End If
        If ((maxSize > 0) _
                    AndAlso (param.Length > maxSize)) Then
            Throw New ArgumentException("Parameter too long " & paramName & " " & maxSize.ToString(CultureInfo.InvariantCulture) & " " & paramName)
        End If
        If (checkForCommas AndAlso param.Contains(",")) Then
            Throw New ArgumentException("Parameter can not contain comma, " & paramName)
        End If
    End Sub

    Private Function GetAppId() As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        'Check to see if the application exists
        Dim sSQL As String = "SELECT ApplicationId FROM " & _TablePrefix & "_Applications WHERE LoweredApplicationName = ?"
        DAP.AddParameter("@ApplicationName", _ApplicationName, DbType.String)
        _AppId = DAP.ExecuteScalar(sSQL)
        'If it doesn't insert a new application into the _Applications table

        If String.IsNullOrEmpty(_AppId) Then
            Dim sNewAppId As String = System.Guid.NewGuid.ToString
            sSQL = "INSERT INTO " & _TablePrefix & "_Applications(ApplicationId, ApplicationName, LoweredApplicationName) " & _
            "VALUES(?, ?, ?)"
            DAP.AddParameter("@ApplicationId", sNewAppId, DbType.String)
            DAP.AddParameter("@ApplicationName", _ApplicationName, DbType.String)
            DAP.AddParameter("@LoweredApplicationName", _ApplicationName.ToLower, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                _AppId = sNewAppId
            Else
                _AppId = Nothing
            End If
        End If
        Return _AppId
    End Function

    Friend Shared Sub CheckArrayParameter(ByRef param() As String, ByVal checkForNull As Boolean, ByVal checkIfEmpty As Boolean, ByVal checkForCommas As Boolean, ByVal maxSize As Integer, ByVal paramName As String)
        If (param Is Nothing) Then
            Throw New ArgumentNullException(paramName)
        End If
        If (param.Length < 1) Then
            Throw New ArgumentException("Parameter array empty, " & paramName)
        End If
        Dim values As Hashtable = New Hashtable(param.Length)
        Dim i As Integer = (param.Length - 1)
        Do While (i >= 0)
            CheckParameter(param(i), checkForNull, checkIfEmpty, checkForCommas, maxSize, (paramName + ("[ " _
                            + (i.ToString(CultureInfo.InvariantCulture) + " ]"))))
            If values.Contains(param(i)) Then
                Throw New ArgumentException("Parameter duplicate array element, " & paramName)
            Else
                values.Add(param(i), param(i))
            End If
            i = (i - 1)
        Loop
    End Sub
#End Region

#Region "Converted Procedures"
    Private Function UsersInRoles_IsUserInRole(ByVal username As String, ByVal rolename As String) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim UserId As String = String.Empty
            Dim RoleId As String = String.Empty
            Dim sSQL As String
            sSQL = "SELECT UserId " & _
            "FROM " & _TablePrefix & "_Users " & _
            "WHERE LoweredUserName = ? AND ApplicationId = ?"
            DAP.AddParameter("@LoweredUserName", username.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            UserId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(UserId) Then
                Return 2
            End If
            sSQL = "SELECT RoleId " & _
            "FROM " & _TablePrefix & "_Roles " & _
            "WHERE LoweredRoleName = ? AND ApplicationId = ?"
            DAP.AddParameter("@LoweredRoleName", rolename.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            RoleId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(RoleId) Then
                Return 3
            End If
            sSQL = "SELECT Count(*) As ExistCount FROM " & _TablePrefix & "_UsersInRoles WHERE UserId = ? AND RoleId = ?"
            DAP.AddParameter("@UserId", UserId, DbType.String)
            DAP.AddParameter("@RoleId", RoleId, DbType.String)
            Dim resultCount As Integer = DAP.ExecuteScalar(sSQL)
            If (resultCount > 0) Then
                Return 1
            Else
                Return 0
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function UsersInRoles_GetRolesForUser(ByVal username As String, ByRef status As Integer) As Generic.List(Of Common.DbDataRecord)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim returnVal As New Generic.List(Of Common.DbDataRecord)
            Dim sSQL As String
            Dim UserId As String = String.Empty
            sSQL = "SELECT UserId " & _
            "FROM " & _TablePrefix & "_Users " & _
            "WHERE LoweredUserName = ? AND ApplicationId = ?"
            DAP.AddParameter("@LoweredUserName", username.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            UserId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(UserId) Then
                status = 1
            Else
                sSQL = "SELECT r.RoleName " & _
                "FROM " & _TablePrefix & "_Roles r, " & _
                _TablePrefix & "_UsersInRoles ur " & _
                "WHERE r.RoleId = ur.RoleId AND r.ApplicationId = ? AND ur.UserId = ? " & _
                "ORDER BY r.RoleName"
                DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                DAP.AddParameter("@UserId", UserId, DbType.String)
                returnVal = DAP.ExecuteReader(sSQL)
                If returnVal.Count = 0 Then
                    status = 1
                Else
                    status = 0
                End If
            End If
            Return returnVal
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Roles_CreateRole(ByVal rolename As String) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "SELECT Count(RoleId) FROM " & _TablePrefix & "_Roles " & _
            "WHERE LoweredRoleName = ? AND ApplicationId = ?"
            DAP.AddParameter("@LoweredRoleName", rolename.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            If DAP.ExecuteScalar(sSQL) >= 1 Then
                Return 1
            Else
                sSQL = "INSERT INTO " & _TablePrefix & "_Roles(RoleId,ApplicationId, " & _
                "RoleName, LoweredRoleName) " & _
                "VALUES(?, ?, ?, ?)"
                DAP.AddParameter("@RoleId", System.Guid.NewGuid.ToString, DbType.String)
                DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                DAP.AddParameter("@RoleName", rolename, DbType.String)
                DAP.AddParameter("@LoweredRoleName", rolename.ToLower, DbType.String)
                If DAP.ExecuteNonQuery(sSQL) = 1 Then
                    Return 0
                Else
                    Return -1
                End If
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Roles_DeleteRole(ByVal rolename As String, ByVal throwOnPopulatedRole As Boolean) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            Dim RoleId As String = String.Empty
            sSQL = "SELECT RoleId FROM " & _TablePrefix & "_Roles " & _
            "WHERE LoweredRoleName = ? AND ApplicationId = ?"
            DAP.AddParameter("@LoweredRoleName", rolename.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            RoleId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(RoleId) Then
                Return 1
            Else
                If throwOnPopulatedRole Then
                    sSQL = "SELECT Count(RoleId) As RoleCount FROM " & _
                    _TablePrefix & "_UsersInRoles " & _
                    "WHERE RoleId = ?"
                    DAP.AddParameter("@RoleId", RoleId, DbType.String)
                    If DAP.ExecuteScalar(sSQL) > 0 Then
                        Return 2
                    End If
                End If
                sSQL = "DELETE FROM " & _TablePrefix & "_UsersInRoles WHERE RoleId = ?"
                DAP.AddParameter("@RoleId", RoleId, DbType.String)
                DAP.ExecuteNonQuery(sSQL)

                sSQL = "DELETE FROM " & _TablePrefix & "_Roles WHERE RoleId = ? AND ApplicationId = ?"
                DAP.AddParameter("@RoleId", RoleId, DbType.String)
                DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                DAP.ExecuteNonQuery(sSQL)
                Return 0
            End If
        Catch
            Return -1
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Roles_RoleExists(ByVal rolename As String) As Boolean
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "SELECT Count(RoleName) As RoleCount " & _
            "FROM " & _TablePrefix & "_Roles " & _
            "WHERE LoweredRoleName = ? AND ApplicationId = ?"
            DAP.AddParameter("@LoweredRoleName", rolename.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            If DAP.ExecuteScalar(sSQL) > 0 Then
                Return True
            Else
                Return False
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Roles_GetAllRoles() As Generic.List(Of Common.DbDataRecord)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "SELECT RoleName " & _
            "FROM " & _TablePrefix & "_Roles " & _
            "WHERE ApplicationId = ? " & _
            "ORDER BY RoleName"
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Dim result As New Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            Return result
        Finally
            DAP.Dispose()
        End Try
    End Function
#End Region
End Class

